package rwt.server.db;

import java.nio.file.*;
import java.sql.*;

import org.slf4j.*;

import rwt.server.RailServer;
import rwt.server.core.*;
import rwt.server.core.User.UserRole;

public class Database
{
	private static final Logger logger = LoggerFactory.getLogger(Database.class);
	
	private static Database database;
	
	/**
	 * Initializes the database. If the SQLite database file indicated by the "DatabaseFile" config property
	 * does not exist, a new database is created with all required tables. The connection pool is also set up.
	 * 
	 * @throws SQLException If a database error occurs while creating a new database.
	 * @throws ClassNotFoundException If the SQLite JDBC driver class cannot be loaded.
	 */
	public static void init() throws SQLException, ClassNotFoundException
	{
		database = new Database();
		
		if (!Files.isRegularFile(Paths.get(Config.databaseFile)))
			database.createNew();
	}
	
	/**
	 * Obtains a connection to the database.
	 * 
	 * @return A connection to the database.
	 * @throws SQLException If connection to database could not be established.
	 */
	public static Connection getConnection() throws SQLException
	{
		return database.getDbConnection();
	}
	
	//--------------------------------------------------------------------------
	
	private DbConnectionPool connectionPool;
	
	private Database() throws ClassNotFoundException
	{
		String driverClassName = "org.sqlite.JDBC";
		Class.forName(driverClassName);
		
		String url = "jdbc:sqlite:" + Config.databaseFile;
		
		connectionPool = new DbConnectionPool(url, driverClassName);
	}
	
	private String getCreateTablesSql()
	{
		return	"CREATE TABLE users (\n" +
				"	id INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
				"	first_name TEXT,\n" +
				"	last_name TEXT,\n" +
				"	username TEXT NOT NULL UNIQUE,\n" +
				"	password TEXT NOT NULL\n" +
				");\n\n" +
				
				"CREATE TABLE roles (\n" +
				"	id INTEGER PRIMARY KEY,\n" +
				"	name TEXT NOT NULL UNIQUE\n" +
				");\n\n" +
				
				"CREATE TABLE user_roles (\n" +
				"	user_id INTEGER NOT NULL,\n" +
				"	role_id INTEGER NOT NULL\n" +
				");\n\n" +
				
				"CREATE UNIQUE INDEX username_idx on users (username ASC);";
	}
	
	private void createNew() throws SQLException
	{
		String createTablesSql = getCreateTablesSql();
		
		logger.info("Excecuting create table SQL:");
		logger.info(createTablesSql);
		
		try (Connection conn = getDbConnection())
		{
			try (Statement stmt = conn.createStatement())
			{
				stmt.executeUpdate(createTablesSql);
			}
			
			DbUtil.createRoles(conn);
			createDefaultUser(conn, "admin", "admin", UserRole.MANAGER);
		}
	}
	
	private User createDefaultUser(Connection conn, String username, String password, UserRole userType)
			throws SQLException
	{
		User user = new User();
		user.setUserName(username);
		user.setPasswordHash(DbUtil.md5(password));
		user.setRole(userType);
		
		try
		{
			DbUtil.createUser(conn, user);
		}
		catch (SQLException e)
		{
			logger.warn("Failed to create user in database.", e);
		}
		
		return user;
	}
	
	private Connection getDbConnection() throws SQLException
	{
		return connectionPool.getConnection();
	}
}
